
 <!DOCTYPE HTML>
<html >
<head>
  <meta charset="UTF-8">
  
    <title>jdbcTemplate实现物理分页 | Typhoon&#39;s code space</title>
    <meta name="viewport" content="width=device-width, initial-scale=1,user-scalable=no">
    
    <meta name="author" content="typhoon">
    

    
    <meta name="description" content="众所周知，在物联网世界里，我们大部分的操作是来自查询，我们面试经常被问到的QPS其实就是针对查询的，">
<meta name="keywords" content="jdbcTemplate分页">
<meta property="og:type" content="article">
<meta property="og:title" content="jdbcTemplate实现物理分页">
<meta property="og:url" content="http://scorpioaeolus.oschina.io/2018/05/26/jdbcTemplate实现分页/index.html">
<meta property="og:site_name" content="Typhoon&#39;s code space">
<meta property="og:description" content="众所周知，在物联网世界里，我们大部分的操作是来自查询，我们面试经常被问到的QPS其实就是针对查询的，">
<meta property="og:image" content="http://scorpioaeolus.oschina.io/typhoon/2018/05/26/jdbcTemplate实现分页/t1.png">
<meta property="og:image" content="http://scorpioaeolus.oschina.io/typhoon/2018/05/26/jdbcTemplate实现分页/t2.png">
<meta property="og:image" content="http://scorpioaeolus.oschina.io/typhoon/2018/05/26/jdbcTemplate实现分页/h4.jpg">
<meta property="og:updated_time" content="2018-05-26T03:23:08.382Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="jdbcTemplate实现物理分页">
<meta name="twitter:description" content="众所周知，在物联网世界里，我们大部分的操作是来自查询，我们面试经常被问到的QPS其实就是针对查询的，">
<meta name="twitter:image" content="http://scorpioaeolus.oschina.io/typhoon/2018/05/26/jdbcTemplate实现分页/t1.png">

    
    <link rel="alternative" href="/atom.xml" title="Typhoon&#39;s code space" type="application/atom+xml">
    
    
    <link rel="icon" href="/typhoon/img/java.png">
    
    
    <link rel="apple-touch-icon" href="/typhoon/img/jacman.jpg">
    <link rel="apple-touch-icon-precomposed" href="/typhoon/img/jacman.jpg">
    
    <link rel="stylesheet" href="/typhoon/css/style.css">
</head>

  <body>
    <header>
      
<div>
		
			<div id="imglogo">
				<a href="/typhoon/"><img src="/typhoon/img/java.png" alt="Typhoon&#39;s code space" title="Typhoon&#39;s code space"/></a>
			</div>
			
			<div id="textlogo">
				<h1 class="site-name"><a href="/typhoon/" title="Typhoon&#39;s code space">Typhoon&#39;s code space</a></h1>
				<h2 class="blog-motto"></h2>
			</div>
			<div class="navbar"><a class="navbutton navmobile" href="#" title="Menu">
			</a></div>
			<nav class="animated">
				<ul>
					<ul>
					 
						<li><a href="/typhoon/">主页</a></li>
					
						<li><a href="/typhoon/archives">归档</a></li>
					
						<li><a href="/typhoon/about">关于</a></li>
					
					<li>
 					
					<form class="search" action="//google.com/search" method="get" accept-charset="utf-8">
						<label>Search</label>
						<input type="search" id="search" name="q" autocomplete="off" maxlength="20" placeholder="Search" />
						<input type="hidden" name="q" value="site:scorpioaeolus.oschina.io">
					</form>
					
					</li>
				</ul>
			</nav>			
</div>
    </header>
    <div id="container">
      <div id="main" class="post" itemscope itemprop="blogPost">
  
	<article itemprop="articleBody"> 
		<header class="article-info clearfix">
  <h1 itemprop="name">
    
      <a href="/typhoon/2018/05/26/jdbcTemplate实现分页/" title="jdbcTemplate实现物理分页" itemprop="url">jdbcTemplate实现物理分页</a>
  </h1>
  <p class="article-author">By
       
		<a href="/typhoon/about" title="typhoon" target="_blank" itemprop="author">typhoon</a>
		
  <p class="article-time">
    <time datetime="2018-05-26T01:22:15.000Z" itemprop="datePublished"> Published 2018-05-26</time>
    
  </p>
</header>
	<div class="article-content">
		
		<div id="toc" class="toc-article">
			<strong class="toc-title">Contents</strong>
		
			<ol class="toc"><li class="toc-item toc-level-6"><a class="toc-link" href="#新建项目-amp-引入依赖"><span class="toc-number">1.</span> <span class="toc-text">新建项目&引入依赖</span></a></li><li class="toc-item toc-level-6"><a class="toc-link" href="#编写实现逻辑"><span class="toc-number">2.</span> <span class="toc-text">编写实现逻辑</span></a></li><li class="toc-item toc-level-6"><a class="toc-link" href="#暴露CustomJdbcTemplate"><span class="toc-number">3.</span> <span class="toc-text">暴露CustomJdbcTemplate</span></a></li><li class="toc-item toc-level-6"><a class="toc-link" href="#编写单元测试"><span class="toc-number">4.</span> <span class="toc-text">编写单元测试</span></a></li><li class="toc-item toc-level-6"><a class="toc-link" href="#总结"><span class="toc-number">5.</span> <span class="toc-text">总结</span></a></li><li class="toc-item toc-level-6"><a class="toc-link" href="#附上本人公众号"><span class="toc-number">6.</span> <span class="toc-text">附上本人公众号</span></a></li></ol>
		
		</div>
		
		<pre><code>众所周知，在物联网世界里，我们大部分的操作是来自查询，我们面试经常被问到的QPS其实就是针对查询的，
</code></pre><a id="more"></a>
<pre><code>   说到查询，根据实际的场景也一般分为单个查询和批量查询，例如：查询会员的详情信息是单个查询，查询会员列表就是
典型的批量查询，说到批量查询那么每次查询的数量就要受限，DB单次查询量限制，网络传输带宽限制，应用程序接收数据量大小
限制等等，那么这时候分页查询变得非常必要，每次查询出指定大小的单页数据，翻页的时候调整分页参数再次查询。
   目前来说，有很多持久层框架都实现了分页功能，比如Hibernate，Mybatis以及专门的分页查询pagehelper等等，也有一部分企业
没有使用持久层框架，直接基于Spring提供的JdbcTemplate操作数据库，查询的时候需要自己写分页查询逻辑，导致了大量重复的代码，
也有一些公司使用了mybatis框架，在mybatis的拦截器层写逻辑完成物理分页。
   接下来，我们就基于JdbcTemplate使用mybatis拦截器物理分页的思想完成分页操作。
</code></pre><h6 id="新建项目-amp-引入依赖"><a href="#新建项目-amp-引入依赖" class="headerlink" title="新建项目&amp;引入依赖"></a>新建项目&amp;引入依赖</h6><pre><code>项目pom文件引入一下基本依赖：
</code></pre><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div></pre></td><td class="code"><pre><div class="line">&lt;dependency&gt;</div><div class="line">	&lt;groupId&gt;mysql&lt;/groupId&gt;</div><div class="line">	&lt;artifactId&gt;mysql-connector-java&lt;/artifactId&gt;</div><div class="line">	&lt;version&gt;5.1.41&lt;/version&gt;</div><div class="line">&lt;/dependency&gt;</div><div class="line">&lt;dependency&gt;</div><div class="line">	&lt;groupId&gt;org.springframework&lt;/groupId&gt;</div><div class="line">	&lt;artifactId&gt;spring-jdbc&lt;/artifactId&gt;</div><div class="line">	&lt;version&gt;4.3.7.RELEASE&lt;/version&gt;</div><div class="line">&lt;/dependency&gt;</div><div class="line">&lt;dependency&gt;</div><div class="line">	&lt;groupId&gt;com.alibaba&lt;/groupId&gt;</div><div class="line">	&lt;artifactId&gt;druid&lt;/artifactId&gt;</div><div class="line">	&lt;version&gt;1.1.2&lt;/version&gt;</div><div class="line">&lt;/dependency&gt;</div><div class="line">&lt;dependency&gt;</div><div class="line">	&lt;groupId&gt;com.alibaba&lt;/groupId&gt;</div><div class="line">	&lt;artifactId&gt;fastjson&lt;/artifactId&gt;</div><div class="line">	&lt;version&gt;1.2.28&lt;/version&gt;</div><div class="line">&lt;/dependency&gt;</div></pre></td></tr></table></figure>
<h6 id="编写实现逻辑"><a href="#编写实现逻辑" class="headerlink" title="编写实现逻辑"></a>编写实现逻辑</h6><pre><code>1）数据操作接口
</code></pre><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div></pre></td><td class="code"><pre><div class="line">/**</div><div class="line"> * 自定义jdbc操作</div><div class="line"> * </div><div class="line"> * @author Typhoon</div><div class="line"> * @date 2018-05-06 14:38 Sunday</div><div class="line"> * @since V2.0.0</div><div class="line"> */</div><div class="line">public interface CustomJdbcOperations extends JdbcOperations &#123;</div><div class="line">	</div><div class="line">	/**</div><div class="line">	 * 分页查询</div><div class="line">	 * </div><div class="line">	 * @author Typhoon</div><div class="line">	 * @date 2018-05-06 14:50 Sunday</div><div class="line">	 * @param sql</div><div class="line">	 * @param rowMapper</div><div class="line">	 * @param pageQuery</div><div class="line">	 * @param args</div><div class="line">	 * @return</div><div class="line">	 * @throws DataAccessException</div><div class="line">	 */</div><div class="line">	&lt;T&gt; PageResult&lt;T&gt; page(String sql, RowMapper&lt;T&gt; rowMapper,PageQuery pageQuery, Object... args) throws DataAccessException;</div><div class="line"></div><div class="line">&#125;</div></pre></td></tr></table></figure>
<pre><code>功能旨在在原来JdbcOperations的基础上增加分页查询功能

2）jdbcTemplate扩展实现
</code></pre><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div><div class="line">32</div><div class="line">33</div><div class="line">34</div><div class="line">35</div><div class="line">36</div><div class="line">37</div><div class="line">38</div><div class="line">39</div><div class="line">40</div><div class="line">41</div><div class="line">42</div><div class="line">43</div><div class="line">44</div><div class="line">45</div></pre></td><td class="code"><pre><div class="line">/**</div><div class="line"> * jdbcTemplate扩展实现</div><div class="line"> * </div><div class="line"> * @author Typhoon</div><div class="line"> * @date 2018-05-26 09:55 Saturday</div><div class="line"> */</div><div class="line">public class CustomJdbcTemplate extends JdbcTemplate implements CustomJdbcOperations &#123;</div><div class="line">	</div><div class="line">	public CustomJdbcTemplate() &#123;</div><div class="line">	&#125;</div><div class="line"></div><div class="line">	/**</div><div class="line">	 * Construct a new JdbcTemplate, given a DataSource to obtain connections from.</div><div class="line">	 * &lt;p&gt;Note: This will not trigger initialization of the exception translator.</div><div class="line">	 * @param dataSource the JDBC DataSource to obtain connections from</div><div class="line">	 */</div><div class="line">	public CustomJdbcTemplate(DataSource dataSource) &#123;</div><div class="line">		super(dataSource);</div><div class="line">	&#125;</div><div class="line">	</div><div class="line">	public CustomJdbcTemplate(DataSource dataSource, boolean lazyInit) &#123;</div><div class="line">		super(dataSource, lazyInit);</div><div class="line">	&#125;</div><div class="line"></div><div class="line">	@Override</div><div class="line">	public &lt;T&gt; PageResult&lt;T&gt; page(String sql, RowMapper&lt;T&gt; rowMapper, PageQuery pageQuery, Object... args)</div><div class="line">			throws DataAccessException &#123;</div><div class="line">		PageInfo page = new PageInfo();</div><div class="line">		int pageSize = pageQuery.getPageSize();</div><div class="line">		if(pageSize &gt;= 5000) &#123;</div><div class="line">			logger.warn(&quot;CustomJdbcTemplate#page pageSize has overhead 5000;pageSize:&quot;+pageSize);</div><div class="line">		&#125;</div><div class="line">		int offset = (pageQuery.getPageIndex() - 1) * pageSize;</div><div class="line">		StringBuffer countSql = new StringBuffer();</div><div class="line">		countSql.append(MySql5PageHepler.getCountString(sql));</div><div class="line">		int totalRows = this.queryForObject(countSql.toString(), args, Integer.class);</div><div class="line">		page.init(totalRows, pageSize, pageQuery.getPageIndex());</div><div class="line">		if(totalRows &lt;= 0) &#123;//查询总数没有数据，就没有必要再去查询具体数据</div><div class="line">			return PageResult.wrap(page, null);</div><div class="line">		&#125;</div><div class="line">		String pageSql = MySql5Dialect.getLimitString(sql, offset, pageSize);</div><div class="line">		List&lt;T&gt; list = this.query(pageSql, rowMapper, args);</div><div class="line">			return PageResult.wrap(page, list);</div><div class="line">		&#125;</div><div class="line">&#125;</div></pre></td></tr></table></figure>
<pre><code>    该类实现了CustomJdbcOperations，拥有了操作数据的功能，并且继承了JdbcTemplate，也即拥有了数据操作的默认实现，
我们写这个类的目的就是在保证原jdbcTemplate功能的基础上扩展出分页查询能力.
    CustomJdbcTemplate是核心类，其分页查询的原理是：
    I）解析原生sql得到查询分页数据的countSql    
    II）查询总行数并初始化分页参数，如果总行数小于等于0，直接返回，不在继续查询业务数据
    III）将原生sql封装成分页查询sql(带limit)
    IV）查询业务数据并封装成分页查询结果返回

3）解析counSql
</code></pre><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div></pre></td><td class="code"><pre><div class="line">/**</div><div class="line"> * 得到查询总数的sql</div><div class="line"> */</div><div class="line">public static String getCountString(String querySelect )</div><div class="line">&#123;</div><div class="line"></div><div class="line">    querySelect = getLineSql( querySelect );</div><div class="line">    int orderIndex = getLastOrderInsertPoint( querySelect );</div><div class="line"></div><div class="line">    int formIndex = getAfterFromInsertPoint( querySelect );</div><div class="line">    String select = querySelect.substring( 0, formIndex );</div><div class="line"></div><div class="line">    // 如果SELECT 中包含 DISTINCT 只能在外层包含COUNT</div><div class="line">    if ( select.toLowerCase().indexOf( &quot;select distinct&quot; ) != -1 || querySelect.toLowerCase().indexOf( &quot;group by&quot; ) != -1 )</div><div class="line">    &#123;</div><div class="line">        return new StringBuffer( querySelect.length() ).append( &quot;select count(1) count from (&quot; ).append( querySelect.substring( 0, orderIndex ) ).append( &quot; ) t&quot; ).toString();</div><div class="line">    &#125;</div><div class="line">    else</div><div class="line">    &#123;</div><div class="line">        return new StringBuffer( querySelect.length() ).append( &quot;select count(1) count &quot; ).append( querySelect.substring( formIndex, orderIndex ) ).toString();</div><div class="line">    &#125;</div><div class="line">&#125;</div></pre></td></tr></table></figure>
<pre><code>4）解析分页查询sql
</code></pre><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div></pre></td><td class="code"><pre><div class="line">/**</div><div class="line">  * 得到分页的SQL</div><div class="line">  * </div><div class="line">  * @param offset 偏移量</div><div class="line">  * @param limit 位置</div><div class="line">  * @return 分页SQL</div><div class="line">  */</div><div class="line"> public static String getLimitString( String querySelect, int offset, int limit )</div><div class="line"> &#123;</div><div class="line"></div><div class="line">     querySelect = getLineSql( querySelect );</div><div class="line"></div><div class="line">     String sql = querySelect + &quot; limit &quot; + offset + &quot; ,&quot; + limit;</div><div class="line"></div><div class="line">     return sql;</div><div class="line"></div><div class="line"> &#125;</div></pre></td></tr></table></figure>
<h6 id="暴露CustomJdbcTemplate"><a href="#暴露CustomJdbcTemplate" class="headerlink" title="暴露CustomJdbcTemplate"></a>暴露CustomJdbcTemplate</h6><pre><code>此处我们为了方便测试，依旧使用之前的无spring配置操作JdbcTemplate:
</code></pre><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div><div class="line">32</div><div class="line">33</div><div class="line">34</div><div class="line">35</div><div class="line">36</div><div class="line">37</div><div class="line">38</div><div class="line">39</div><div class="line">40</div><div class="line">41</div><div class="line">42</div><div class="line">43</div><div class="line">44</div><div class="line">45</div><div class="line">46</div><div class="line">47</div><div class="line">48</div><div class="line">49</div><div class="line">50</div><div class="line">51</div><div class="line">52</div></pre></td><td class="code"><pre><div class="line">/**</div><div class="line"> * 简化数据源配置类</div><div class="line"> * </div><div class="line"> * @author Typhoon</div><div class="line"> * @date 2017-08-22 11:27 Tuesday</div><div class="line"> * @since V1.3.1</div><div class="line"> */</div><div class="line">public class DataSourceUtils &#123;</div><div class="line"></div><div class="line">    public static Properties p       = null;</div><div class="line">    public static String     confile = &quot;jdbc.properties&quot;;</div><div class="line"></div><div class="line">    static &#123;</div><div class="line">        p = new Properties();</div><div class="line">        InputStream inputStream = null;</div><div class="line">        try &#123;</div><div class="line">            inputStream = DataSourceUtils.class.getClassLoader().getResourceAsStream(confile);</div><div class="line">            inputStream = new BufferedInputStream(inputStream);</div><div class="line">            p.load(inputStream);</div><div class="line">        &#125; catch (Exception e) &#123;</div><div class="line">            e.printStackTrace();</div><div class="line">        &#125; finally &#123;</div><div class="line">            try &#123;</div><div class="line">                if (inputStream != null) &#123;</div><div class="line">                    inputStream.close();</div><div class="line">                &#125;</div><div class="line">            &#125; catch (IOException e) &#123;</div><div class="line">                e.printStackTrace();</div><div class="line">            &#125;</div><div class="line">        &#125;</div><div class="line">    &#125;</div><div class="line"></div><div class="line">    private static DataSource getDataSource() &#123;</div><div class="line">        try &#123;</div><div class="line">            return DruidDataSourceFactory.createDataSource(p);</div><div class="line">        &#125; catch (Exception e) &#123;</div><div class="line">            e.printStackTrace();</div><div class="line">        &#125;</div><div class="line">        return null;</div><div class="line">    &#125;</div><div class="line">    </div><div class="line">    /**</div><div class="line">     * 获取自定义jdbcTemplate</div><div class="line">     * </div><div class="line">     * @return</div><div class="line">     */</div><div class="line">    public static CustomJdbcTemplate getCustomJdbcTemplate() &#123;</div><div class="line">        DataSource   dataSource   = getDataSource();</div><div class="line">        CustomJdbcTemplate customJdbcTemplate = new CustomJdbcTemplate(dataSource);</div><div class="line">        return customJdbcTemplate;</div><div class="line">    &#125;</div><div class="line">&#125;</div></pre></td></tr></table></figure>
<h6 id="编写单元测试"><a href="#编写单元测试" class="headerlink" title="编写单元测试"></a>编写单元测试</h6><pre><code>1）Dao操作层
</code></pre><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div><div class="line">32</div><div class="line">33</div><div class="line">34</div><div class="line">35</div><div class="line">36</div><div class="line">37</div><div class="line">38</div><div class="line">39</div><div class="line">40</div><div class="line">41</div><div class="line">42</div><div class="line">43</div><div class="line">44</div><div class="line">45</div><div class="line">46</div><div class="line">47</div></pre></td><td class="code"><pre><div class="line">/**</div><div class="line"> * 用户操作dao</div><div class="line"> * </div><div class="line"> * @author Typhoon</div><div class="line"> * @date 2017-08-22 15:32 Tuesday</div><div class="line"> * @since V1.3.1</div><div class="line"> */</div><div class="line">public class UserDao &#123;</div><div class="line">	/**</div><div class="line">	 * 获取jdbcTemplate</div><div class="line">	 */</div><div class="line">	private CustomJdbcTemplate jdbcTemplate = DataSourceUtils.getCustomJdbcTemplate();</div><div class="line"></div><div class="line">	/**</div><div class="line">	* 原生JdbcTemplate查询</div><div class="line">	*/</div><div class="line">	public User selectById(Long id) &#123;</div><div class="line">		List&lt;User&gt; list = jdbcTemplate.query(&quot;  select * from User where id = ? limit 1  &quot;, BeanPropertyRowMapper.newInstance(User.class),id);</div><div class="line">		if(null == list || list.isEmpty()) &#123;</div><div class="line">			return null;</div><div class="line">		&#125;</div><div class="line">		return list.get(0);</div><div class="line">	&#125;</div><div class="line">	</div><div class="line">	/**</div><div class="line">	* 扩展接口分页查询</div><div class="line">	*/</div><div class="line">	public PageResult&lt;User&gt; page(PageQuery pageQuery,Map&lt;String,Object&gt; params) &#123;</div><div class="line">		StringBuilder sql = new StringBuilder();</div><div class="line">		List&lt;Object&gt; paramList = new ArrayList&lt;&gt;();</div><div class="line">		sql.append(&quot; select * from User where 1 = 1 &quot;);</div><div class="line">		Object id = params.get(&quot;id&quot;);</div><div class="line">		if(null != id) &#123;</div><div class="line">			sql.append(&quot; and &quot;).append(&quot; id =?  &quot;);</div><div class="line">			paramList.add(id);</div><div class="line">		&#125;</div><div class="line">		Object name = params.get(&quot;name&quot;);</div><div class="line">		if(null != name) &#123;</div><div class="line">			sql.append(&quot; and  &quot;).append(&quot;	name = ?	&quot;);</div><div class="line">			paramList.add(name);</div><div class="line">		&#125;</div><div class="line">		</div><div class="line">		sql.append(&quot;  order by createTime desc  &quot;);</div><div class="line">		</div><div class="line">		return this.jdbcTemplate.page(sql.toString(), BeanPropertyRowMapper.newInstance(User.class), pageQuery, paramList.toArray());</div><div class="line">	&#125;</div><div class="line">&#125;</div></pre></td></tr></table></figure>
<pre><code>2）单元测试
原生查询：
</code></pre><img src="/typhoon/2018/05/26/jdbcTemplate实现分页/t1.png" alt="test" title="test"> 
<pre><code>扩展分页查询:
</code></pre><img src="/typhoon/2018/05/26/jdbcTemplate实现分页/t2.png" alt="test" title="test"> 
<pre><code>可以看到我们扩展后的CustomJdbcTemplate在保留原生JdbcTemplate功能的基础上，实现了DB查询的通用分页。
</code></pre><h6 id="总结"><a href="#总结" class="headerlink" title="总结"></a>总结</h6><pre><code>    经过上边的描述，我们已经基于原生JdbcTemplate扩展并实现了分页的功能，在一些不想使用持久层框架
或者觉得持久层框架太重的项目中可以考虑对原生JdbcTemplate扩展来实现一些业务通用或者定制化的功能。
当然这只是一种实现方式，另外一种实现方式是我们把Dao层通用的操作提出来抽象到BaseDao层来实现，具体的业务Dao
只需继承BaseDao就可以使用更新查询的功能，同样可以达到和扩展JdbcTemplate一样的效果。此篇暂且讲到这里，
希望给大家在日常开发中带来帮助！
</code></pre><p><strong><em>PS:原创不易,多多支持!</em></strong></p>
<h6 id="附上本人公众号"><a href="#附上本人公众号" class="headerlink" title="附上本人公众号"></a>附上本人公众号</h6><img src="/typhoon/2018/05/26/jdbcTemplate实现分页/h4.jpg" alt="jdbcTemplate" title="jdbcTemplate">
  
	</div>
		<footer class="article-footer clearfix">
<div class="article-catetags">

<div class="article-categories">
  <span></span>
  <a class="article-category-link" href="/typhoon/categories/spring/">spring</a>
</div>


  <div class="article-tags">
  
  <span></span> <a href="/typhoon/tags/jdbcTemplate分页/">jdbcTemplate分页</a>
  </div>

</div>



	<div class="article-share" id="share">
	
	  <div data-url="http://scorpioaeolus.oschina.io/2018/05/26/jdbcTemplate实现分页/" data-title="jdbcTemplate实现物理分页 | Typhoon&#39;s code space" data-tsina="null" class="share clearfix">
	  </div>
	
	</div>


</footer>

   	       
	</article>
	
<nav class="article-nav clearfix">
 
 <div class="prev" >
 <a href="/typhoon/2018/05/26/list使用中的坑/" title="jdk源码分析之List--使用中的坑">
  <strong>上一篇：</strong><br/>
  <span>
  jdk源码分析之List--使用中的坑</span>
</a>
</div>


<div class="next">
<a href="/typhoon/2018/05/25/字符串分割性能对比/"  title="字符串分割性能对比">
 <strong>下一篇：</strong><br/> 
 <span>字符串分割性能对比
</span>
</a>
</div>

</nav>

	


</div>  
      <div class="openaside"><a class="navbutton" href="#" title="Show Sidebar"></a></div>

  <div id="toc" class="toc-aside">
  <strong class="toc-title">Contents</strong>
 
 <ol class="toc"><li class="toc-item toc-level-6"><a class="toc-link" href="#新建项目-amp-引入依赖"><span class="toc-number">1.</span> <span class="toc-text">新建项目&引入依赖</span></a></li><li class="toc-item toc-level-6"><a class="toc-link" href="#编写实现逻辑"><span class="toc-number">2.</span> <span class="toc-text">编写实现逻辑</span></a></li><li class="toc-item toc-level-6"><a class="toc-link" href="#暴露CustomJdbcTemplate"><span class="toc-number">3.</span> <span class="toc-text">暴露CustomJdbcTemplate</span></a></li><li class="toc-item toc-level-6"><a class="toc-link" href="#编写单元测试"><span class="toc-number">4.</span> <span class="toc-text">编写单元测试</span></a></li><li class="toc-item toc-level-6"><a class="toc-link" href="#总结"><span class="toc-number">5.</span> <span class="toc-text">总结</span></a></li><li class="toc-item toc-level-6"><a class="toc-link" href="#附上本人公众号"><span class="toc-number">6.</span> <span class="toc-text">附上本人公众号</span></a></li></ol>
 
  </div>

<div id="asidepart">
<div class="closeaside"><a class="closebutton" href="#" title="Hide Sidebar"></a></div>
<aside class="clearfix">

  


  
<div class="categorieslist">
	<p class="asidetitle">Categories</p>
		<ul>
		
		  
			<li><a href="/typhoon/categories/dubbo/" title="dubbo">dubbo<sup>8</sup></a></li>
		  
		
		  
			<li><a href="/typhoon/categories/java性能优化/" title="java性能优化">java性能优化<sup>17</sup></a></li>
		  
		
		  
			<li><a href="/typhoon/categories/java源码分析/" title="java源码分析">java源码分析<sup>1</sup></a></li>
		  
		
		  
			<li><a href="/typhoon/categories/motan/" title="motan">motan<sup>2</sup></a></li>
		  
		
		  
			<li><a href="/typhoon/categories/spring/" title="spring">spring<sup>11</sup></a></li>
		  
		
		  
			<li><a href="/typhoon/categories/springboot/" title="springboot">springboot<sup>28</sup></a></li>
		  
		
		  
			<li><a href="/typhoon/categories/springmvc/" title="springmvc">springmvc<sup>1</sup></a></li>
		  
		
		  
			<li><a href="/typhoon/categories/容器/" title="容器">容器<sup>1</sup></a></li>
		  
		
		  
			<li><a href="/typhoon/categories/工具/" title="工具">工具<sup>7</sup></a></li>
		  
		
		  
			<li><a href="/typhoon/categories/性能优化/" title="性能优化">性能优化<sup>5</sup></a></li>
		  
		
		  
			<li><a href="/typhoon/categories/架构/" title="架构">架构<sup>8</sup></a></li>
		  
		
		  
			<li><a href="/typhoon/categories/案例分析/" title="案例分析">案例分析<sup>13</sup></a></li>
		  
		
		  
			<li><a href="/typhoon/categories/缓存/" title="缓存">缓存<sup>4</sup></a></li>
		  
		
		  
			<li><a href="/typhoon/categories/设计模式/" title="设计模式">设计模式<sup>3</sup></a></li>
		  
		
		  
			<li><a href="/typhoon/categories/面试题/" title="面试题">面试题<sup>1</sup></a></li>
		  
		
		</ul>
</div>


  
<div class="tagslist">
	<p class="asidetitle">Tags</p>
		<ul class="clearfix">
		
			
				<li><a href="/typhoon/tags/springboot/" title="springboot">springboot<sup>28</sup></a></li>
			
		
			
				<li><a href="/typhoon/tags/java性能优化/" title="java性能优化">java性能优化<sup>9</sup></a></li>
			
		
			
				<li><a href="/typhoon/tags/限流/" title="限流">限流<sup>3</sup></a></li>
			
		
			
				<li><a href="/typhoon/tags/Spring5/" title="Spring5">Spring5<sup>3</sup></a></li>
			
		
			
				<li><a href="/typhoon/tags/设计模式/" title="设计模式">设计模式<sup>3</sup></a></li>
			
		
			
				<li><a href="/typhoon/tags/案例分析/" title="案例分析">案例分析<sup>2</sup></a></li>
			
		
			
				<li><a href="/typhoon/tags/性能优化/" title="性能优化">性能优化<sup>2</sup></a></li>
			
		
			
				<li><a href="/typhoon/tags/Spring/" title="Spring">Spring<sup>2</sup></a></li>
			
		
			
				<li><a href="/typhoon/tags/dubbo/" title="dubbo">dubbo<sup>2</sup></a></li>
			
		
			
				<li><a href="/typhoon/tags/分页/" title="分页">分页<sup>2</sup></a></li>
			
		
			
				<li><a href="/typhoon/tags/并发/" title="并发">并发<sup>2</sup></a></li>
			
		
			
				<li><a href="/typhoon/tags/异常拦截/" title="异常拦截">异常拦截<sup>1</sup></a></li>
			
		
			
				<li><a href="/typhoon/tags/dubbo分布式事务/" title="dubbo分布式事务">dubbo分布式事务<sup>1</sup></a></li>
			
		
			
				<li><a href="/typhoon/tags/自带图片/" title="自带图片">自带图片<sup>1</sup></a></li>
			
		
			
				<li><a href="/typhoon/tags/svn导出mvn项目/" title="svn导出mvn项目">svn导出mvn项目<sup>1</sup></a></li>
			
		
			
				<li><a href="/typhoon/tags/dubbo过滤器/" title="dubbo过滤器">dubbo过滤器<sup>1</sup></a></li>
			
		
			
				<li><a href="/typhoon/tags/排序性能对比/" title="排序性能对比">排序性能对比<sup>1</sup></a></li>
			
		
			
				<li><a href="/typhoon/tags/ListenableFuture/" title="ListenableFuture">ListenableFuture<sup>1</sup></a></li>
			
		
			
				<li><a href="/typhoon/tags/PropertyChangeListener/" title="PropertyChangeListener">PropertyChangeListener<sup>1</sup></a></li>
			
		
			
				<li><a href="/typhoon/tags/jdbcTemplate高可用/" title="jdbcTemplate高可用">jdbcTemplate高可用<sup>1</sup></a></li>
			
		
		</ul>
</div>


  <div class="linkslist">
  <p class="asidetitle">Links</p>
    <ul>
        
          <li>
            
            	<a href="https://coderq.com" target="_blank" title="一个面向程序员交流分享的新一代社区">码农圈</a>
            
          </li>
        
          <li>
            
            	<a href="http://wuchong.me" target="_blank" title="Jark&#39;s Blog">Jark&#39;s Blog</a>
            
          </li>
        
    </ul>
</div>

  


  <div class="rsspart">
	<a href="/atom.xml" target="_blank" title="rss">RSS</a>
</div>

  <div class="weiboshow">
  <p class="asidetitle">Weibo</p>
    <iframe width="100%" height="119" class="share_self"  frameborder="0" scrolling="no" src="http://widget.weibo.com/weiboshow/index.php?language=&width=0&height=119&fansRow=2&ptype=1&speed=0&skin=9&isTitle=1&noborder=1&isWeibo=0&isFans=0&uid=null&verifier=b3593ceb&dpc=1"></iframe>
</div>


</aside>
</div>
    </div>
    <footer><div id="footer" >
	
	<div class="line">
		<span></span>
		<div class="author"></div>
	</div>
	
	
	<section class="info">
		<p> Hello ,I&#39;m a java coder. <br/>
			This is my blog,believe it or not.</p>
	</section>
	 
	<div class="social-font" class="clearfix">
		
		
		
		
		
		
		
		
		
		
		<a href="mailto:ScorpioAeolus@163.com" target="_blank" class="icon-email" title="Email Me"></a>
		
	</div>
			
		

		<p class="copyright">
		Powered by <a href="http://hexo.io" target="_blank" title="hexo">hexo</a> and Theme by <a href="https://github.com/wuchong/jacman" target="_blank" title="Jacman">Jacman</a> © 2019 
		
		<a href="/typhoon/about" target="_blank" title="typhoon">typhoon</a>
		
		
		</p>
</div>
</footer>
    <script src="/typhoon/js/jquery-2.0.3.min.js"></script>
<script src="/typhoon/js/jquery.imagesloaded.min.js"></script>
<script src="/typhoon/js/gallery.js"></script>
<script src="/typhoon/js/jquery.qrcode-0.12.0.min.js"></script>

<script type="text/javascript">
$(document).ready(function(){ 
  $('.navbar').click(function(){
    $('header nav').toggleClass('shownav');
  });
  var myWidth = 0;
  function getSize(){
    if( typeof( window.innerWidth ) == 'number' ) {
      myWidth = window.innerWidth;
    } else if( document.documentElement && document.documentElement.clientWidth) {
      myWidth = document.documentElement.clientWidth;
    };
  };
  var m = $('#main'),
      a = $('#asidepart'),
      c = $('.closeaside'),
      o = $('.openaside');
  c.click(function(){
    a.addClass('fadeOut').css('display', 'none');
    o.css('display', 'block').addClass('fadeIn');
    m.addClass('moveMain');
  });
  o.click(function(){
    o.css('display', 'none').removeClass('beforeFadeIn');
    a.css('display', 'block').removeClass('fadeOut').addClass('fadeIn');      
    m.removeClass('moveMain');
  });
  $(window).scroll(function(){
    o.css("top",Math.max(80,260-$(this).scrollTop()));
  });
  
  $(window).resize(function(){
    getSize(); 
    if (myWidth >= 1024) {
      $('header nav').removeClass('shownav');
    }else{
      m.removeClass('moveMain');
      a.css('display', 'block').removeClass('fadeOut');
      o.css('display', 'none');
      
      $('#toc.toc-aside').css('display', 'none');
        
    }
  });
});
</script>

<script type="text/javascript">
$(document).ready(function(){ 
  var ai = $('.article-content>iframe'),
      ae = $('.article-content>embed'),
      t  = $('#toc'),
      ta = $('#toc.toc-aside'),
      o  = $('.openaside'),
      c  = $('.closeaside');
  if(ai.length>0){
    ai.wrap('<div class="video-container" />');
  };
  if(ae.length>0){
   ae.wrap('<div class="video-container" />');
  };
  c.click(function(){
    ta.css('display', 'block').addClass('fadeIn');
  });
  o.click(function(){
    ta.css('display', 'none');
  });
  $(window).scroll(function(){
    ta.css("top",Math.max(140,320-$(this).scrollTop()));
  });
});
</script>


<script type="text/javascript">
$(document).ready(function(){ 
  var $this = $('.share'),
      url = $this.attr('data-url'),
      encodedUrl = encodeURIComponent(url),
      title = $this.attr('data-title'),
      tsina = $this.attr('data-tsina'),
      description = $this.attr('description');
  var html = [
  '<div class="hoverqrcode clearfix"></div>',
  '<a class="overlay" id="qrcode"></a>',
  '<a href="https://www.facebook.com/sharer.php?u=' + encodedUrl + '" class="article-share-facebook" target="_blank" title="Facebook"></a>',
  '<a href="https://twitter.com/intent/tweet?url=' + encodedUrl + '" class="article-share-twitter" target="_blank" title="Twitter"></a>',
  '<a href="#qrcode" class="article-share-qrcode" title="微信"></a>',
  '<a href="http://widget.renren.com/dialog/share?resourceUrl=' + encodedUrl + '&srcUrl=' + encodedUrl + '&title=' + title +'" class="article-share-renren" target="_blank" title="人人"></a>',
  '<a href="http://service.weibo.com/share/share.php?title='+title+'&url='+encodedUrl +'&ralateUid='+ tsina +'&searchPic=true&style=number' +'" class="article-share-weibo" target="_blank" title="微博"></a>',
  '<span title="Share to"></span>'
  ].join('');
  $this.append(html);

  $('.hoverqrcode').hide();

  var myWidth = 0;
  function updatehoverqrcode(){
    if( typeof( window.innerWidth ) == 'number' ) {
      myWidth = window.innerWidth;
    } else if( document.documentElement && document.documentElement.clientWidth) {
      myWidth = document.documentElement.clientWidth;
    };
    var qrsize = myWidth > 1024 ? 200:100;
    var options = {render: 'image', size: qrsize, fill: '#2ca6cb', text: url, radius: 0.5, quiet: 1};
    var p = $('.article-share-qrcode').position();
    $('.hoverqrcode').empty().css('width', qrsize).css('height', qrsize)
                          .css('left', p.left-qrsize/2+20).css('top', p.top-qrsize-10)
                          .qrcode(options);
  };
  $(window).resize(function(){
    $('.hoverqrcode').hide();
  });
  $('.article-share-qrcode').click(function(){
    updatehoverqrcode();
    $('.hoverqrcode').toggle();
  });
  $('.article-share-qrcode').hover(function(){}, function(){
      $('.hoverqrcode').hide();
  });
});   
</script>









<link rel="stylesheet" href="/typhoon/fancybox/jquery.fancybox.css" media="screen" type="text/css">
<script src="/typhoon/fancybox/jquery.fancybox.pack.js"></script>
<script type="text/javascript">
$(document).ready(function(){ 
  $('.article-content').each(function(i){
    $(this).find('img').each(function(){
      if ($(this).parent().hasClass('fancybox')) return;
      var alt = this.alt;
      if (alt) $(this).after('<span class="caption">' + alt + '</span>');
      $(this).wrap('<a href="' + this.src + '" title="' + alt + '" class="fancybox"></a>');
    });
    $(this).find('.fancybox').each(function(){
      $(this).attr('rel', 'article' + i);
    });
  });
  if($.fancybox){
    $('.fancybox').fancybox();
  }
}); 
</script>



<!-- Analytics Begin -->



<script>
var _hmt = _hmt || [];
(function() {
  var hm = document.createElement("script");
  hm.src = "//hm.baidu.com/hm.js?e6d1f421bbc9962127a50488f9ed37d1";
  var s = document.getElementsByTagName("script")[0]; 
  s.parentNode.insertBefore(hm, s);
})();
</script>



<!-- Analytics End -->

<!-- Totop Begin -->

	<div id="totop">
	<a title="Back to Top"><img src="/typhoon/img/scrollup.png"/></a>
	</div>
	<script src="/typhoon/js/totop.js"></script>

<!-- Totop End -->

<!-- MathJax Begin -->
<!-- mathjax config similar to math.stackexchange -->


<!-- MathJax End -->

<!-- Tiny_search Begin -->

<!-- Tiny_search End -->

  </body>
</html>
